<!DOCTYPE html>
<html lang="zh-CN">
  <head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width,initial-scale=1">
    <title>MySql学习第三天 | 凌歆的博客</title>
    <meta name="generator" content="VuePress 1.9.5">
    <link rel="icon" href="/img/favicon.ico">
    <meta name="description" content="web前端技术博客,专注web前端学习与总结。JavaScript,js,ES6,TypeScript,vue,React,python,css3,html5,Node,git,github等技术文章。">
    <meta name="keywords" content="前端博客,个人技术博客,前端,前端开发,前端框架,web前端,前端面试题,技术文档,学习,面试,JavaScript,js,ES6,TypeScript,vue,python,css3,html5,Node,git,github,markdown">
    <meta name="baidu-site-verification" content="7F55weZDDc">
    <meta name="theme-color" content="#11a8cd">
    
    <link rel="preload" href="/assets/css/0.styles.d9c8cf8c.css" as="style"><link rel="preload" href="/assets/js/app.57550e13.js" as="script"><link rel="preload" href="/assets/js/2.f014c35f.js" as="script"><link rel="preload" href="/assets/js/93.c8b8e3d6.js" as="script"><link rel="prefetch" href="/assets/js/10.03a98811.js"><link rel="prefetch" href="/assets/js/100.145e708e.js"><link rel="prefetch" href="/assets/js/101.098ca5c8.js"><link rel="prefetch" href="/assets/js/102.4d45c69a.js"><link rel="prefetch" href="/assets/js/103.499de505.js"><link rel="prefetch" href="/assets/js/104.c2c1b9b6.js"><link rel="prefetch" href="/assets/js/105.4e9b0663.js"><link rel="prefetch" href="/assets/js/106.9bb8cf6f.js"><link rel="prefetch" href="/assets/js/107.a81b32d7.js"><link rel="prefetch" href="/assets/js/108.81511e67.js"><link rel="prefetch" href="/assets/js/109.6a6741bf.js"><link rel="prefetch" href="/assets/js/11.e8729182.js"><link rel="prefetch" href="/assets/js/110.de657fe7.js"><link rel="prefetch" href="/assets/js/111.b092af6d.js"><link rel="prefetch" href="/assets/js/112.2dca5709.js"><link rel="prefetch" href="/assets/js/113.dbed1fac.js"><link rel="prefetch" href="/assets/js/114.e95a0131.js"><link rel="prefetch" href="/assets/js/115.2e3e8285.js"><link rel="prefetch" href="/assets/js/116.e64f7fec.js"><link rel="prefetch" href="/assets/js/117.583552c9.js"><link rel="prefetch" href="/assets/js/118.aa5d18f0.js"><link rel="prefetch" href="/assets/js/119.0c7b52d7.js"><link rel="prefetch" href="/assets/js/12.d63acec2.js"><link rel="prefetch" href="/assets/js/120.13f86281.js"><link rel="prefetch" href="/assets/js/121.e2b5b7f9.js"><link rel="prefetch" href="/assets/js/122.5252216a.js"><link rel="prefetch" href="/assets/js/123.8b8a3503.js"><link rel="prefetch" href="/assets/js/124.60df855c.js"><link rel="prefetch" href="/assets/js/125.f073ab1c.js"><link rel="prefetch" href="/assets/js/126.7b73de6a.js"><link rel="prefetch" href="/assets/js/127.4e25971c.js"><link rel="prefetch" href="/assets/js/128.0edf0e06.js"><link rel="prefetch" href="/assets/js/129.5cb5a70a.js"><link rel="prefetch" href="/assets/js/13.2259ef71.js"><link rel="prefetch" href="/assets/js/130.00247fda.js"><link rel="prefetch" href="/assets/js/131.d6fc003a.js"><link rel="prefetch" href="/assets/js/132.1a32b18f.js"><link rel="prefetch" href="/assets/js/133.e46c0193.js"><link rel="prefetch" href="/assets/js/134.a6ad681f.js"><link rel="prefetch" href="/assets/js/135.2fcbe137.js"><link rel="prefetch" href="/assets/js/136.f69bf451.js"><link rel="prefetch" href="/assets/js/137.b9ef2fcc.js"><link rel="prefetch" href="/assets/js/138.7fdd9feb.js"><link rel="prefetch" href="/assets/js/139.a3a37c91.js"><link rel="prefetch" href="/assets/js/14.0d6b23b9.js"><link rel="prefetch" href="/assets/js/140.a7c013ae.js"><link rel="prefetch" href="/assets/js/141.956c36ce.js"><link rel="prefetch" href="/assets/js/142.5aacfe42.js"><link rel="prefetch" href="/assets/js/143.57d691ef.js"><link rel="prefetch" href="/assets/js/144.b1e5766e.js"><link rel="prefetch" href="/assets/js/145.463284b7.js"><link rel="prefetch" href="/assets/js/146.6cc6420c.js"><link rel="prefetch" href="/assets/js/147.5b78c5a2.js"><link rel="prefetch" href="/assets/js/148.8d1d7679.js"><link rel="prefetch" href="/assets/js/149.4e0eb213.js"><link rel="prefetch" href="/assets/js/15.f6fde69d.js"><link rel="prefetch" href="/assets/js/150.b183de37.js"><link rel="prefetch" href="/assets/js/151.729ae770.js"><link rel="prefetch" href="/assets/js/152.b1297018.js"><link rel="prefetch" href="/assets/js/153.bb991bca.js"><link rel="prefetch" href="/assets/js/154.6f2286aa.js"><link rel="prefetch" href="/assets/js/155.6a9c7e4d.js"><link rel="prefetch" href="/assets/js/156.f7bd535d.js"><link rel="prefetch" href="/assets/js/157.64c0065c.js"><link rel="prefetch" href="/assets/js/158.d31ae949.js"><link rel="prefetch" href="/assets/js/159.7daea8a7.js"><link rel="prefetch" href="/assets/js/16.7be21beb.js"><link rel="prefetch" href="/assets/js/160.33cc971a.js"><link rel="prefetch" href="/assets/js/161.1676442a.js"><link rel="prefetch" href="/assets/js/162.71378046.js"><link rel="prefetch" href="/assets/js/163.99e49959.js"><link rel="prefetch" href="/assets/js/164.306f07d2.js"><link rel="prefetch" href="/assets/js/165.170977c7.js"><link rel="prefetch" href="/assets/js/166.f8602525.js"><link rel="prefetch" href="/assets/js/167.eea5c001.js"><link rel="prefetch" href="/assets/js/168.0146a311.js"><link rel="prefetch" href="/assets/js/169.05f9c9d9.js"><link rel="prefetch" href="/assets/js/17.2543f471.js"><link rel="prefetch" href="/assets/js/170.25d602b2.js"><link rel="prefetch" href="/assets/js/171.1ada26a6.js"><link rel="prefetch" href="/assets/js/172.2a0f697c.js"><link rel="prefetch" href="/assets/js/173.230d8ca8.js"><link rel="prefetch" href="/assets/js/174.e3758a2b.js"><link rel="prefetch" href="/assets/js/175.0f19f8ea.js"><link rel="prefetch" href="/assets/js/176.bf70d37d.js"><link rel="prefetch" href="/assets/js/177.4400f272.js"><link rel="prefetch" href="/assets/js/178.85af7b89.js"><link rel="prefetch" href="/assets/js/179.4cfaaaa2.js"><link rel="prefetch" href="/assets/js/18.394415f8.js"><link rel="prefetch" href="/assets/js/180.449c8409.js"><link rel="prefetch" href="/assets/js/181.73276924.js"><link rel="prefetch" href="/assets/js/182.5fab25cc.js"><link rel="prefetch" href="/assets/js/183.ecd4934d.js"><link rel="prefetch" href="/assets/js/184.246092cf.js"><link rel="prefetch" href="/assets/js/185.c671cd86.js"><link rel="prefetch" href="/assets/js/186.49bd1963.js"><link rel="prefetch" href="/assets/js/187.1fb32764.js"><link rel="prefetch" href="/assets/js/188.7c6885e9.js"><link rel="prefetch" href="/assets/js/189.a8701dec.js"><link rel="prefetch" href="/assets/js/19.ca7db8ea.js"><link rel="prefetch" href="/assets/js/190.ae3d3bde.js"><link rel="prefetch" href="/assets/js/191.ad7cd44e.js"><link rel="prefetch" href="/assets/js/192.bc443842.js"><link rel="prefetch" href="/assets/js/193.e4755764.js"><link rel="prefetch" href="/assets/js/194.814112ce.js"><link rel="prefetch" href="/assets/js/195.1c0aaa80.js"><link rel="prefetch" href="/assets/js/196.a6bd7add.js"><link rel="prefetch" href="/assets/js/20.4e91cd31.js"><link rel="prefetch" href="/assets/js/21.f095e6e1.js"><link rel="prefetch" href="/assets/js/22.72f4b8ab.js"><link rel="prefetch" href="/assets/js/23.e94eb9d2.js"><link rel="prefetch" href="/assets/js/24.0eae7d6f.js"><link rel="prefetch" href="/assets/js/25.36157609.js"><link rel="prefetch" href="/assets/js/26.dc5b6cba.js"><link rel="prefetch" href="/assets/js/27.c19b7b63.js"><link rel="prefetch" href="/assets/js/28.3aceae59.js"><link rel="prefetch" href="/assets/js/29.536091b3.js"><link rel="prefetch" href="/assets/js/3.d11119c4.js"><link rel="prefetch" href="/assets/js/30.821a4a30.js"><link rel="prefetch" href="/assets/js/31.5238784c.js"><link rel="prefetch" href="/assets/js/32.10bc7179.js"><link rel="prefetch" href="/assets/js/33.c1ed69c9.js"><link rel="prefetch" href="/assets/js/34.8d384231.js"><link rel="prefetch" href="/assets/js/35.d8890e52.js"><link rel="prefetch" href="/assets/js/36.eca37ad5.js"><link rel="prefetch" href="/assets/js/37.802549e3.js"><link rel="prefetch" href="/assets/js/38.53841770.js"><link rel="prefetch" href="/assets/js/39.7ae44409.js"><link rel="prefetch" href="/assets/js/4.493b9bca.js"><link rel="prefetch" href="/assets/js/40.ce56364a.js"><link rel="prefetch" href="/assets/js/41.85f0114b.js"><link rel="prefetch" href="/assets/js/42.714da6e2.js"><link rel="prefetch" href="/assets/js/43.fee4437c.js"><link rel="prefetch" href="/assets/js/44.b039b68b.js"><link rel="prefetch" href="/assets/js/45.96a55605.js"><link rel="prefetch" href="/assets/js/46.956ffb03.js"><link rel="prefetch" href="/assets/js/47.147d9218.js"><link rel="prefetch" href="/assets/js/48.ff787b40.js"><link rel="prefetch" href="/assets/js/49.871eee87.js"><link rel="prefetch" href="/assets/js/5.e0ba07d7.js"><link rel="prefetch" href="/assets/js/50.99b7c29e.js"><link rel="prefetch" href="/assets/js/51.8ecbf4ba.js"><link rel="prefetch" href="/assets/js/52.53d91d7e.js"><link rel="prefetch" href="/assets/js/53.af8a0d2a.js"><link rel="prefetch" href="/assets/js/54.a2f848b5.js"><link rel="prefetch" href="/assets/js/55.267e9947.js"><link rel="prefetch" href="/assets/js/56.0b201c40.js"><link rel="prefetch" href="/assets/js/57.cbdfd6f7.js"><link rel="prefetch" href="/assets/js/58.636c0c3f.js"><link rel="prefetch" href="/assets/js/59.fc9216c4.js"><link rel="prefetch" href="/assets/js/6.b98373f2.js"><link rel="prefetch" href="/assets/js/60.34f16fc4.js"><link rel="prefetch" href="/assets/js/61.4d130bd8.js"><link rel="prefetch" href="/assets/js/62.927caa10.js"><link rel="prefetch" href="/assets/js/63.a451fee0.js"><link rel="prefetch" href="/assets/js/64.32a5d47b.js"><link rel="prefetch" href="/assets/js/65.bc099429.js"><link rel="prefetch" href="/assets/js/66.3953050d.js"><link rel="prefetch" href="/assets/js/67.e934823b.js"><link rel="prefetch" href="/assets/js/68.4bcb52b0.js"><link rel="prefetch" href="/assets/js/69.bede18c8.js"><link rel="prefetch" href="/assets/js/7.8de8df2d.js"><link rel="prefetch" href="/assets/js/70.fce1daac.js"><link rel="prefetch" href="/assets/js/71.ea0a8c5d.js"><link rel="prefetch" href="/assets/js/72.15382c9e.js"><link rel="prefetch" href="/assets/js/73.848364d3.js"><link rel="prefetch" href="/assets/js/74.5fd08a50.js"><link rel="prefetch" href="/assets/js/75.c937c70d.js"><link rel="prefetch" href="/assets/js/76.a0dacd5a.js"><link rel="prefetch" href="/assets/js/77.4019cc23.js"><link rel="prefetch" href="/assets/js/78.9676a9e1.js"><link rel="prefetch" href="/assets/js/79.e46e10d5.js"><link rel="prefetch" href="/assets/js/8.2098eb96.js"><link rel="prefetch" href="/assets/js/80.987e83cb.js"><link rel="prefetch" href="/assets/js/81.eb98ff36.js"><link rel="prefetch" href="/assets/js/82.623561a7.js"><link rel="prefetch" href="/assets/js/83.e256d909.js"><link rel="prefetch" href="/assets/js/84.7d06a550.js"><link rel="prefetch" href="/assets/js/85.b8d9879e.js"><link rel="prefetch" href="/assets/js/86.31c4581b.js"><link rel="prefetch" href="/assets/js/87.e06ee05e.js"><link rel="prefetch" href="/assets/js/88.81ef718d.js"><link rel="prefetch" href="/assets/js/89.ba237d2f.js"><link rel="prefetch" href="/assets/js/9.36092fd0.js"><link rel="prefetch" href="/assets/js/90.ad2c9d92.js"><link rel="prefetch" href="/assets/js/91.a661f52d.js"><link rel="prefetch" href="/assets/js/92.1c28035b.js"><link rel="prefetch" href="/assets/js/94.b6bc4a44.js"><link rel="prefetch" href="/assets/js/95.92cea882.js"><link rel="prefetch" href="/assets/js/96.5373491b.js"><link rel="prefetch" href="/assets/js/97.bb39efc8.js"><link rel="prefetch" href="/assets/js/98.af7a030f.js"><link rel="prefetch" href="/assets/js/99.07dc87d8.js">
    <link rel="stylesheet" href="/assets/css/0.styles.d9c8cf8c.css">
  </head>
  <body class="theme-mode-light">
    <div id="app" data-server-rendered="true"><div class="theme-container sidebar-open have-rightmenu"><header class="navbar blur"><div title="目录" class="sidebar-button"><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" role="img" viewBox="0 0 448 512" class="icon"><path fill="currentColor" d="M436 124H12c-6.627 0-12-5.373-12-12V80c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12zm0 160H12c-6.627 0-12-5.373-12-12v-32c0-6.627 5.373-12 12-12h424c6.627 0 12 5.373 12 12v32c0 6.627-5.373 12-12 12z"></path></svg></div> <a href="/" class="home-link router-link-active"><img src="https://lingxin-tanhua.oss-cn-shanghai.aliyuncs.com/QQ%E6%88%AA%E5%9B%BE20220503092755.png" alt="凌歆的博客" class="logo"> <span class="site-name can-hide">凌歆的博客</span></a> <div class="links"><div class="search-box"><input aria-label="Search" autocomplete="off" spellcheck="false" value=""> <!----></div> <nav class="nav-links can-hide"><div class="nav-item"><a href="/" class="nav-link">首页</a></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="前端" class="dropdown-title"><a href="/web/" class="link-title">前端</a> <span class="title" style="display:none;">前端</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>学习笔记</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/note/html/" class="nav-link">HTML笔记</a></li><li class="dropdown-subitem"><a href="/note/js-low/" class="nav-link">JavaScript基础笔记</a></li><li class="dropdown-subitem"><a href="/note/js-high/" class="nav-link">JavaScript高阶笔记</a></li><li class="dropdown-subitem"><a href="/note/js-dom-bom/" class="nav-link">JavaScript DOM和BOM笔记</a></li><li class="dropdown-subitem"><a href="/note/jquery/" class="nav-link">jQuery笔记</a></li><li class="dropdown-subitem"><a href="/note/ajax/" class="nav-link">Ajax笔记</a></li><li class="dropdown-subitem"><a href="/note/nodejs/" class="nav-link">NodeJs笔记</a></li><li class="dropdown-subitem"><a href="/note/reg/" class="nav-link">正则表达式笔记</a></li><li class="dropdown-subitem"><a href="/note/vue/" class="nav-link">Vue笔记</a></li><li class="dropdown-subitem"><a href="/note/react/" class="nav-link">react笔记</a></li><li class="dropdown-subitem"><a href="/note/wx/" class="nav-link">微信小程序笔记</a></li><li class="dropdown-subitem"><a href="/note/uniapp/" class="nav-link">uniapp笔记</a></li><li class="dropdown-subitem"><a href="/note/echarts/" class="nav-link">echarts笔记</a></li><li class="dropdown-subitem"><a href="/note/git/" class="nav-link">Git笔记</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="后端" class="dropdown-title"><a href="/back/" class="link-title">后端</a> <span class="title" style="display:none;">后端</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/note/java/" class="nav-link">JAVA</a></li><li class="dropdown-item"><!----> <a href="/note/javaweb/" class="nav-link">JavaWeb</a></li><li class="dropdown-item"><!----> <a href="/note/mysql/" class="nav-link">MySql</a></li><li class="dropdown-item"><!----> <a href="/note/ssm/" class="nav-link">ssm</a></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="算法" class="dropdown-title"><a href="/algor/" class="link-title">算法</a> <span class="title" style="display:none;">算法</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/note/sf/" class="nav-link">算法训练营</a></li><li class="dropdown-item"><!----> <a href="/note/js-sf/" class="nav-link">js数据结构与算法</a></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="面试" class="dropdown-title"><a href="/mianshi/" class="link-title">面试</a> <span class="title" style="display:none;">面试</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/note/lqb/" class="nav-link">蓝桥杯备战</a></li><li class="dropdown-item"><!----> <a href="/note/ms/" class="nav-link">前端面试</a></li><li class="dropdown-item"><!----> <a href="/note/hmms/" class="nav-link">黑马面试</a></li><li class="dropdown-item"><!----> <a href="/note/xzms/" class="nav-link">校招零距离面试</a></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="解决方案" class="dropdown-title"><a href="/resolve/" class="link-title">解决方案</a> <span class="title" style="display:none;">解决方案</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/note/qd-resolve/" class="nav-link">前端解决方案</a></li><li class="dropdown-item"><!----> <a href="/note/hd-resolve/" class="nav-link">后端解决方案</a></li></ul></div></div><div class="nav-item"><a href="/pages/beb6c0bd8a66cea6/" class="nav-link">收藏</a></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="索引" class="dropdown-title"><a href="/archives/" class="link-title">索引</a> <span class="title" style="display:none;">索引</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/categories/" class="nav-link">分类</a></li><li class="dropdown-item"><!----> <a href="/tags/" class="nav-link">标签</a></li><li class="dropdown-item"><!----> <a href="/archives/" class="nav-link">归档</a></li></ul></div></div> <a href="https://github.com/linxin1123/vuepress-blog" target="_blank" rel="noopener noreferrer" class="repo-link">
    GitHub
    <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></nav></div></header> <div class="sidebar-mask"></div> <div class="sidebar-hover-trigger"></div> <aside class="sidebar" style="display:none;"><div class="blogger"><img src="https://lingxin-tanhua.oss-cn-shanghai.aliyuncs.com/QQ%E6%88%AA%E5%9B%BE20220503092755.png"> <div class="blogger-info"><h3>lingxin</h3> <span>凌歆</span></div></div> <nav class="nav-links"><div class="nav-item"><a href="/" class="nav-link">首页</a></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="前端" class="dropdown-title"><a href="/web/" class="link-title">前端</a> <span class="title" style="display:none;">前端</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><h4>学习笔记</h4> <ul class="dropdown-subitem-wrapper"><li class="dropdown-subitem"><a href="/note/html/" class="nav-link">HTML笔记</a></li><li class="dropdown-subitem"><a href="/note/js-low/" class="nav-link">JavaScript基础笔记</a></li><li class="dropdown-subitem"><a href="/note/js-high/" class="nav-link">JavaScript高阶笔记</a></li><li class="dropdown-subitem"><a href="/note/js-dom-bom/" class="nav-link">JavaScript DOM和BOM笔记</a></li><li class="dropdown-subitem"><a href="/note/jquery/" class="nav-link">jQuery笔记</a></li><li class="dropdown-subitem"><a href="/note/ajax/" class="nav-link">Ajax笔记</a></li><li class="dropdown-subitem"><a href="/note/nodejs/" class="nav-link">NodeJs笔记</a></li><li class="dropdown-subitem"><a href="/note/reg/" class="nav-link">正则表达式笔记</a></li><li class="dropdown-subitem"><a href="/note/vue/" class="nav-link">Vue笔记</a></li><li class="dropdown-subitem"><a href="/note/react/" class="nav-link">react笔记</a></li><li class="dropdown-subitem"><a href="/note/wx/" class="nav-link">微信小程序笔记</a></li><li class="dropdown-subitem"><a href="/note/uniapp/" class="nav-link">uniapp笔记</a></li><li class="dropdown-subitem"><a href="/note/echarts/" class="nav-link">echarts笔记</a></li><li class="dropdown-subitem"><a href="/note/git/" class="nav-link">Git笔记</a></li></ul></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="后端" class="dropdown-title"><a href="/back/" class="link-title">后端</a> <span class="title" style="display:none;">后端</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/note/java/" class="nav-link">JAVA</a></li><li class="dropdown-item"><!----> <a href="/note/javaweb/" class="nav-link">JavaWeb</a></li><li class="dropdown-item"><!----> <a href="/note/mysql/" class="nav-link">MySql</a></li><li class="dropdown-item"><!----> <a href="/note/ssm/" class="nav-link">ssm</a></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="算法" class="dropdown-title"><a href="/algor/" class="link-title">算法</a> <span class="title" style="display:none;">算法</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/note/sf/" class="nav-link">算法训练营</a></li><li class="dropdown-item"><!----> <a href="/note/js-sf/" class="nav-link">js数据结构与算法</a></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="面试" class="dropdown-title"><a href="/mianshi/" class="link-title">面试</a> <span class="title" style="display:none;">面试</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/note/lqb/" class="nav-link">蓝桥杯备战</a></li><li class="dropdown-item"><!----> <a href="/note/ms/" class="nav-link">前端面试</a></li><li class="dropdown-item"><!----> <a href="/note/hmms/" class="nav-link">黑马面试</a></li><li class="dropdown-item"><!----> <a href="/note/xzms/" class="nav-link">校招零距离面试</a></li></ul></div></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="解决方案" class="dropdown-title"><a href="/resolve/" class="link-title">解决方案</a> <span class="title" style="display:none;">解决方案</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/note/qd-resolve/" class="nav-link">前端解决方案</a></li><li class="dropdown-item"><!----> <a href="/note/hd-resolve/" class="nav-link">后端解决方案</a></li></ul></div></div><div class="nav-item"><a href="/pages/beb6c0bd8a66cea6/" class="nav-link">收藏</a></div><div class="nav-item"><div class="dropdown-wrapper"><button type="button" aria-label="索引" class="dropdown-title"><a href="/archives/" class="link-title">索引</a> <span class="title" style="display:none;">索引</span> <span class="arrow right"></span></button> <ul class="nav-dropdown" style="display:none;"><li class="dropdown-item"><!----> <a href="/categories/" class="nav-link">分类</a></li><li class="dropdown-item"><!----> <a href="/tags/" class="nav-link">标签</a></li><li class="dropdown-item"><!----> <a href="/archives/" class="nav-link">归档</a></li></ul></div></div> <a href="https://github.com/linxin1123/vuepress-blog" target="_blank" rel="noopener noreferrer" class="repo-link">
    GitHub
    <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></a></nav>  <ul class="sidebar-links"><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading"><span>学习笔记</span> <span class="arrow right"></span></p> <!----></section></li><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading"><span>Java笔记</span> <span class="arrow right"></span></p> <!----></section></li><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading"><span>JavaWeb笔记</span> <span class="arrow right"></span></p> <!----></section></li><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading open"><span>MySql笔记</span> <span class="arrow down"></span></p> <ul class="sidebar-links sidebar-group-items"><li><a href="/pages/533d88/" class="sidebar-link">mysql学习第一天</a></li><li><a href="/pages/e13f8c/" class="sidebar-link">MySQL学习第二天</a></li><li><a href="/pages/774997/" aria-current="page" class="active sidebar-link">MySql学习第三天</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level2"><a href="/pages/774997/#_1-多表查询-查询多个表" class="sidebar-link">1.多表查询（查询多个表）</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/pages/774997/#_1-1-产生一个笛卡儿积" class="sidebar-link">1.1 产生一个笛卡儿积</a></li><li class="sidebar-sub-header level4"><a href="/pages/774997/#问题" class="sidebar-link">问题</a></li><li class="sidebar-sub-header level3"><a href="/pages/774997/#_1-2-多表查询的分类" class="sidebar-link">1.2 多表查询的分类</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/pages/774997/#_2-内连接" class="sidebar-link">2. 内连接</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/pages/774997/#_2-1-隐式内连接" class="sidebar-link">2.1 隐式内连接</a></li><li class="sidebar-sub-header level3"><a href="/pages/774997/#_2-2-显示内连接" class="sidebar-link">2.2 显示内连接</a></li><li class="sidebar-sub-header level3"><a href="/pages/774997/#_2-3-内连接的注意点" class="sidebar-link">2.3 内连接的注意点</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/pages/774997/#_3-外连接" class="sidebar-link">3 外连接</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/pages/774997/#_3-1-左外连接" class="sidebar-link">3.1 左外连接</a></li><li class="sidebar-sub-header level3"><a href="/pages/774997/#_3-2-右外连接" class="sidebar-link">3.2 右外连接</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/pages/774997/#_4-子查询" class="sidebar-link">4.子查询</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/pages/774997/#_4-1-子查询的结果的使用情况" class="sidebar-link">4.1 子查询的结果的使用情况</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/pages/774997/#_5-多表查询案例" class="sidebar-link">5.多表查询案例</a></li><li class="sidebar-sub-header level2"><a href="/pages/774997/#_6-事务" class="sidebar-link">6.事务</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/pages/774997/#_6-1-事务的介绍" class="sidebar-link">6.1 事务的介绍</a></li><li class="sidebar-sub-header level3"><a href="/pages/774997/#_6-2-事务提交的两种方式" class="sidebar-link">6.2 事务提交的两种方式</a></li><li class="sidebar-sub-header level3"><a href="/pages/774997/#_6-3-事务的四大特征" class="sidebar-link">6.3 事务的四大特征</a></li><li class="sidebar-sub-header level3"><a href="/pages/774997/#_6-4-事务的不同隔离级别" class="sidebar-link">6.4 事务的不同隔离级别</a></li></ul></li><li class="sidebar-sub-header level2"><a href="/pages/774997/#_7-dcl-管理用户-授权" class="sidebar-link">7.DCL(管理用户，授权)</a><ul class="sidebar-sub-headers"><li class="sidebar-sub-header level3"><a href="/pages/774997/#_7-1管理用户" class="sidebar-link">7.1管理用户</a></li><li class="sidebar-sub-header level3"><a href="/pages/774997/#_7-2-mysql忘记了root用户的密码" class="sidebar-link">7.2 mysql忘记了root用户的密码</a></li><li class="sidebar-sub-header level3"><a href="/pages/774997/#_7-3-管理权限" class="sidebar-link">7.3 管理权限</a></li></ul></li></ul></li></ul></section></li><li><section class="sidebar-group collapsable depth-0"><p class="sidebar-heading"><span>SSM笔记</span> <span class="arrow right"></span></p> <!----></section></li></ul> </aside> <div><main class="page"><div class="theme-vdoing-wrapper "><div class="articleInfo-wrap" data-v-06225672><div class="articleInfo" data-v-06225672><ul class="breadcrumbs" data-v-06225672><li data-v-06225672><a href="/" title="首页" class="iconfont icon-home router-link-active" data-v-06225672></a></li> <li data-v-06225672><a href="/back/#后端" data-v-06225672>后端</a></li><li data-v-06225672><a href="/back/#MySql笔记" data-v-06225672>MySql笔记</a></li></ul> <div class="info" data-v-06225672><div title="作者" class="author iconfont icon-touxiang" data-v-06225672><a href="https://github.com/linxin1123" target="_blank" title="作者" class="beLink" data-v-06225672>lingxin</a></div> <div title="创建时间" class="date iconfont icon-riqi" data-v-06225672><a href="javascript:;" data-v-06225672>2023-01-25</a></div> <!----></div></div></div> <!----> <div class="content-wrapper"><div class="right-menu-wrapper"><div class="right-menu-margin"><div class="right-menu-title">目录</div> <div class="right-menu-content"></div></div></div> <h1><img src="">MySql学习第三天<!----></h1>  <div class="theme-vdoing-content content__default"><h1 id="mysql学习第三天"><a href="#mysql学习第三天" class="header-anchor">#</a> MySql学习第三天</h1> <p>@[toc]</p> <h2 id="_1-多表查询-查询多个表"><a href="#_1-多表查询-查询多个表" class="header-anchor">#</a> 1.多表查询（查询多个表）</h2> <h3 id="_1-1-产生一个笛卡儿积"><a href="#_1-1-产生一个笛卡儿积" class="header-anchor">#</a> 1.1 产生一个笛卡儿积</h3> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">CREATE</span> <span class="token keyword">DATABASE</span>  <span class="token keyword">IF</span> <span class="token operator">NOT</span> <span class="token keyword">EXISTS</span> db2<span class="token punctuation">;</span>

<span class="token keyword">USE</span> db2<span class="token punctuation">;</span>

<span class="token comment"># 创建部门表</span>
<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> dept<span class="token punctuation">(</span>
	 id <span class="token keyword">INT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token keyword">AUTO_INCREMENT</span><span class="token punctuation">,</span>
	 NAME <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>


<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> dept <span class="token punctuation">(</span>NAME<span class="token punctuation">)</span> <span class="token keyword">VALUES</span> <span class="token punctuation">(</span><span class="token string">'开发部'</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token string">'市场部'</span><span class="token punctuation">)</span><span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token string">'财务部'</span><span class="token punctuation">)</span><span class="token punctuation">;</span> 
<span class="token comment"># 创建员工表</span>
<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> emp <span class="token punctuation">(</span>
	 id <span class="token keyword">INT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token keyword">AUTO_INCREMENT</span><span class="token punctuation">,</span>
	 NAME <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
	 gender <span class="token keyword">CHAR</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token comment">-- 性别</span>
	 salary <span class="token keyword">DOUBLE</span><span class="token punctuation">,</span> <span class="token comment">-- 工资</span>
	 join_date <span class="token keyword">DATE</span><span class="token punctuation">,</span> <span class="token comment">-- 入职日期</span>
	 dept_id <span class="token keyword">INT</span><span class="token punctuation">,</span>
	 <span class="token keyword">FOREIGN</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span>dept_id<span class="token punctuation">)</span> <span class="token keyword">REFERENCES</span> dept<span class="token punctuation">(</span>id<span class="token punctuation">)</span> <span class="token comment">-- 外键，关联部门表(部门表的主键)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>
 
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> emp<span class="token punctuation">(</span>NAME<span class="token punctuation">,</span>gender<span class="token punctuation">,</span>salary<span class="token punctuation">,</span>join_date<span class="token punctuation">,</span>dept_id<span class="token punctuation">)</span> <span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token string">'孙悟空'</span><span class="token punctuation">,</span><span class="token string">'男'</span><span class="token punctuation">,</span><span class="token number">7200</span><span class="token punctuation">,</span><span class="token string">'2013-02-24'</span><span class="token punctuation">,</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> emp<span class="token punctuation">(</span>NAME<span class="token punctuation">,</span>gender<span class="token punctuation">,</span>salary<span class="token punctuation">,</span>join_date<span class="token punctuation">,</span>dept_id<span class="token punctuation">)</span> <span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token string">'猪八戒'</span><span class="token punctuation">,</span><span class="token string">'男'</span><span class="token punctuation">,</span><span class="token number">3600</span><span class="token punctuation">,</span><span class="token string">'2010-12-02'</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> emp<span class="token punctuation">(</span>NAME<span class="token punctuation">,</span>gender<span class="token punctuation">,</span>salary<span class="token punctuation">,</span>join_date<span class="token punctuation">,</span>dept_id<span class="token punctuation">)</span> <span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token string">'唐僧'</span><span class="token punctuation">,</span><span class="token string">'男'</span><span class="token punctuation">,</span><span class="token number">9000</span><span class="token punctuation">,</span><span class="token string">'2008-08-08'</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> emp<span class="token punctuation">(</span>NAME<span class="token punctuation">,</span>gender<span class="token punctuation">,</span>salary<span class="token punctuation">,</span>join_date<span class="token punctuation">,</span>dept_id<span class="token punctuation">)</span> <span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token string">'白骨精'</span><span class="token punctuation">,</span><span class="token string">'女'</span><span class="token punctuation">,</span><span class="token number">5000</span><span class="token punctuation">,</span><span class="token string">'2015-10-07'</span><span class="token punctuation">,</span><span class="token number">3</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> emp<span class="token punctuation">(</span>NAME<span class="token punctuation">,</span>gender<span class="token punctuation">,</span>salary<span class="token punctuation">,</span>join_date<span class="token punctuation">,</span>dept_id<span class="token punctuation">)</span> <span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token string">'蜘蛛精'</span><span class="token punctuation">,</span><span class="token string">'女'</span><span class="token punctuation">,</span><span class="token number">4500</span><span class="token punctuation">,</span><span class="token string">'2011-03-14'</span><span class="token punctuation">,</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">;</span>


<span class="token keyword">DROP</span> <span class="token keyword">TABLE</span> emp<span class="token punctuation">;</span>

<span class="token keyword">DROP</span> <span class="token keyword">TABLE</span> dept<span class="token punctuation">;</span>

<span class="token keyword">SHOW</span> <span class="token keyword">TABLES</span><span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> <span class="token operator">*</span><span class="token keyword">FROM</span> emp<span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> <span class="token operator">*</span><span class="token keyword">FROM</span> dept<span class="token punctuation">;</span>

<span class="token comment">-- 查询笛卡儿积</span>

<span class="token keyword">SELECT</span> <span class="token operator">*</span><span class="token keyword">FROM</span> emp<span class="token punctuation">,</span>dept<span class="token punctuation">;</span>

</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br><span class="line-number">20</span><br><span class="line-number">21</span><br><span class="line-number">22</span><br><span class="line-number">23</span><br><span class="line-number">24</span><br><span class="line-number">25</span><br><span class="line-number">26</span><br><span class="line-number">27</span><br><span class="line-number">28</span><br><span class="line-number">29</span><br><span class="line-number">30</span><br><span class="line-number">31</span><br><span class="line-number">32</span><br><span class="line-number">33</span><br><span class="line-number">34</span><br><span class="line-number">35</span><br><span class="line-number">36</span><br><span class="line-number">37</span><br><span class="line-number">38</span><br><span class="line-number">39</span><br><span class="line-number">40</span><br><span class="line-number">41</span><br><span class="line-number">42</span><br><span class="line-number">43</span><br><span class="line-number">44</span><br></div></div><ul><li>效果图</li></ul> <p><img src="https://img-blog.csdnimg.cn/e211a4428995493c979bf47dcf73d25d.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5YeM5q2G,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center" alt="在这里插入图片描述"></p> <h4 id="问题"><a href="#问题" class="header-anchor">#</a> 问题</h4> <ul><li><strong>该笛卡尔集存在重复并且不正确的数据，需要消除</strong></li></ul> <h3 id="_1-2-多表查询的分类"><a href="#_1-2-多表查询的分类" class="header-anchor">#</a> 1.2 多表查询的分类</h3> <ul><li><strong>内连接</strong></li> <li><strong>外连接</strong></li> <li><strong>子查询</strong></li></ul> <h2 id="_2-内连接"><a href="#_2-内连接" class="header-anchor">#</a> 2. 内连接</h2> <ul><li><strong>==定义：用左边表的记录去匹配右边表的记录，符合条件才显示，例如：从表.外键=主表.主键==</strong></li> <li>就是查询交集的部分</li></ul> <h3 id="_2-1-隐式内连接"><a href="#_2-1-隐式内连接" class="header-anchor">#</a> 2.1 隐式内连接</h3> <ul><li><p><strong>看不到JOIN关键字，条件使用where</strong></p></li> <li><p>语法</p></li></ul> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">select</span> <span class="token operator">*</span><span class="token keyword">from</span> 左表，右表 <span class="token keyword">where</span> 条件
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><ul><li>示例</li></ul> <p><img src="https://img-blog.csdnimg.cn/21dd11af02fe4877a9a8cf3edd360fc1.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5YeM5q2G,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center" alt="在这里插入图片描述"></p> <ul><li>示例：查询员工表的姓名，性别，和部门名称</li></ul> <p><img src="https://img-blog.csdnimg.cn/85ad7f50739647d5a0426c941e01059c.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5YeM5q2G,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center" alt="在这里插入图片描述"></p> <h3 id="_2-2-显示内连接"><a href="#_2-2-显示内连接" class="header-anchor">#</a> 2.2 显示内连接</h3> <ul><li><strong>使用INNER JOIN...ON语句，可以省略INNER</strong></li> <li>语法</li></ul> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">select</span> 字段列表 <span class="token keyword">from</span> 左表<span class="token punctuation">[</span><span class="token keyword">inner</span><span class="token punctuation">]</span> <span class="token keyword">JOIN</span> 右表 <span class="token keyword">on</span> 条件
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><ul><li>示例</li></ul> <p><img src="https://img-blog.csdnimg.cn/45ae362ee07641d58cc7775a739b5363.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5YeM5q2G,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center" alt="在这里插入图片描述"></p> <h3 id="_2-3-内连接的注意点"><a href="#_2-3-内连接的注意点" class="header-anchor">#</a> 2.3 内连接的注意点</h3> <ul><li><strong>从哪些表中查询</strong></li> <li><strong>条件是什么</strong></li> <li><strong>查询哪些字段</strong></li></ul> <h2 id="_3-外连接"><a href="#_3-外连接" class="header-anchor">#</a> 3 外连接</h2> <h3 id="_3-1-左外连接"><a href="#_3-1-左外连接" class="header-anchor">#</a> 3.1 左外连接</h3> <ul><li><p><strong>用左边表的记录去匹配右边表的记录，如果符合则显示，否则显示null</strong></p></li> <li><p><strong>查询的是左表的所有数据及其交集部分（内连接的基础上保证左表的记录完整）</strong></p></li> <li><p>语法</p></li></ul> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">select</span> 字段列表 <span class="token keyword">from</span> 左表 <span class="token keyword">left</span> <span class="token keyword">JOIN</span> 右表 <span class="token keyword">ON</span> 条件
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><ul><li>示例</li></ul> <p><img src="https://img-blog.csdnimg.cn/8de2d329ffbb4e329fd36e09850ad246.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5YeM5q2G,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center" alt="在这里插入图片描述"></p> <h3 id="_3-2-右外连接"><a href="#_3-2-右外连接" class="header-anchor">#</a> 3.2 右外连接</h3> <ul><li><p><strong>查询的是右表的所有数据及其交集部分</strong></p></li> <li><p>语法</p></li></ul> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">select</span> 字段列表 <span class="token keyword">from</span> 左表 <span class="token keyword">right</span> <span class="token keyword">JOIN</span> 右表 <span class="token keyword">ON</span> 条件
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><ul><li>示例</li></ul> <p><img src="https://img-blog.csdnimg.cn/b3fd506638f3490597257e0023b53da4.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5YeM5q2G,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center" alt="在这里插入图片描述"></p> <h2 id="_4-子查询"><a href="#_4-子查询" class="header-anchor">#</a> 4.子查询</h2> <ul><li><p><strong>==查询中嵌套查询，嵌套的查询称为子查询==</strong></p></li> <li><p><strong>一个查询的结果作为另一个查询的条件</strong></p></li> <li><p>示例:查询工资最高的一条记录</p></li></ul> <p><img src="https://img-blog.csdnimg.cn/dfc9a82d8bcb4250b61ed30f6ffdbad8.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5YeM5q2G,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center" alt="在这里插入图片描述"></p> <h3 id="_4-1-子查询的结果的使用情况"><a href="#_4-1-子查询的结果的使用情况" class="header-anchor">#</a> 4.1 子查询的结果的使用情况</h3> <ul><li><p><strong>结果是单行单列的</strong></p> <ul><li><strong>结果可以用来作为条件，使用运算符判断（&gt; &lt;  = &gt;= &lt;=）</strong></li> <li>示例：查询部门为财务部的员工信息</li></ul> <p><img src="https://img-blog.csdnimg.cn/31cd9afbcd6745d4b24cb536d7b9a684.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5YeM5q2G,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center" alt="在这里插入图片描述"></p></li> <li><p><strong>结果是多行单列的</strong></p> <ul><li><p><strong>结果可以作为条件，使用运算符in（结果）判断</strong></p></li> <li><p>示例</p></li></ul> <p><img src="https://img-blog.csdnimg.cn/601ddbc576c14543ad12cef16ef86eaf.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5YeM5q2G,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center" alt="在这里插入图片描述"></p></li> <li><p><strong>结果是多行多列的</strong></p> <ul><li><strong>==结果可以作为一张虚拟表参与查询==</strong></li> <li>示例</li></ul></li></ul> <p><img src="https://img-blog.csdnimg.cn/d11cb9a4d13a440bbea7f51cbe49f9b6.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5YeM5q2G,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center" alt="在这里插入图片描述"></p> <p><img src="https://img-blog.csdnimg.cn/19b0ff1d99224701a59ad427cd2dadc5.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5YeM5q2G,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center" alt="在这里插入图片描述"></p> <ul><li>方法是等价的</li></ul> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">-- 子查询结果为多行多列的情况</span>

<span class="token comment">-- 查询在2011-11-11后入职的员工信息和部门信息</span>

<span class="token keyword">SELECT</span> <span class="token operator">*</span><span class="token keyword">FROM</span> emp <span class="token keyword">WHERE</span> emp<span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>join_date<span class="token punctuation">`</span></span><span class="token operator">&gt;</span><span class="token string">'2011-11-11'</span><span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> <span class="token operator">*</span><span class="token keyword">FROM</span> dept t1<span class="token punctuation">,</span><span class="token punctuation">(</span><span class="token keyword">SELECT</span> <span class="token operator">*</span><span class="token keyword">FROM</span> emp <span class="token keyword">WHERE</span> emp<span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>join_date<span class="token punctuation">`</span></span><span class="token operator">&gt;</span><span class="token string">'2011-11-11'</span><span class="token punctuation">)</span> t2 <span class="token keyword">WHERE</span> t1<span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>id<span class="token punctuation">`</span></span><span class="token operator">=</span>t2<span class="token punctuation">.</span>dept_id<span class="token punctuation">;</span>

<span class="token comment">-- d</span>
<span class="token keyword">SELECT</span> <span class="token operator">*</span><span class="token keyword">FROM</span> emp t1<span class="token punctuation">,</span>dept t2 <span class="token keyword">WHERE</span> t1<span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>join_date<span class="token punctuation">`</span></span><span class="token operator">&gt;</span><span class="token string">'2011-11-11'</span> <span class="token operator">AND</span> t1<span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>dept_id<span class="token punctuation">`</span></span><span class="token operator">=</span>t2<span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>id<span class="token punctuation">`</span></span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br></div></div><h2 id="_5-多表查询案例"><a href="#_5-多表查询案例" class="header-anchor">#</a> 5.多表查询案例</h2> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">CREATE</span> <span class="token keyword">DATABASE</span> db3<span class="token punctuation">;</span>
<span class="token keyword">USE</span> db3<span class="token punctuation">;</span>

<span class="token comment">-- 部门表</span>
<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> dept <span class="token punctuation">(</span>
  id <span class="token keyword">INT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">,</span> <span class="token comment">-- 部门id</span>
  dname <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">50</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token comment">-- 部门名称</span>
  loc <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">50</span><span class="token punctuation">)</span> <span class="token comment">-- 部门所在地</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment">-- 添加4个部门</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> dept<span class="token punctuation">(</span>id<span class="token punctuation">,</span>dname<span class="token punctuation">,</span>loc<span class="token punctuation">)</span> <span class="token keyword">VALUES</span> 
<span class="token punctuation">(</span><span class="token number">10</span><span class="token punctuation">,</span><span class="token string">'教研部'</span><span class="token punctuation">,</span><span class="token string">'北京'</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">,</span><span class="token string">'学工部'</span><span class="token punctuation">,</span><span class="token string">'上海'</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token punctuation">(</span><span class="token number">30</span><span class="token punctuation">,</span><span class="token string">'销售部'</span><span class="token punctuation">,</span><span class="token string">'广州'</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token punctuation">(</span><span class="token number">40</span><span class="token punctuation">,</span><span class="token string">'财务部'</span><span class="token punctuation">,</span><span class="token string">'深圳'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>



<span class="token comment">-- 职务表，职务名称，职务描述</span>
<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> job <span class="token punctuation">(</span>
  id <span class="token keyword">INT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">,</span>
  jname <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">20</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
  description <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">50</span><span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment">-- 添加4个职务</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> job <span class="token punctuation">(</span>id<span class="token punctuation">,</span> jname<span class="token punctuation">,</span> description<span class="token punctuation">)</span> <span class="token keyword">VALUES</span>
<span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token string">'董事长'</span><span class="token punctuation">,</span> <span class="token string">'管理整个公司，接单'</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span> <span class="token string">'经理'</span><span class="token punctuation">,</span> <span class="token string">'管理部门员工'</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token punctuation">(</span><span class="token number">3</span><span class="token punctuation">,</span> <span class="token string">'销售员'</span><span class="token punctuation">,</span> <span class="token string">'向客人推销产品'</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token punctuation">(</span><span class="token number">4</span><span class="token punctuation">,</span> <span class="token string">'文员'</span><span class="token punctuation">,</span> <span class="token string">'使用办公软件'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>



<span class="token comment">-- 员工表</span>
<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> emp <span class="token punctuation">(</span>
  id <span class="token keyword">INT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">,</span> <span class="token comment">-- 员工id</span>
  ename <span class="token keyword">VARCHAR</span><span class="token punctuation">(</span><span class="token number">50</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token comment">-- 员工姓名</span>
  job_id <span class="token keyword">INT</span><span class="token punctuation">,</span> <span class="token comment">-- 职务id</span>
  mgr <span class="token keyword">INT</span> <span class="token punctuation">,</span> <span class="token comment">-- 上级领导</span>
  joindate <span class="token keyword">DATE</span><span class="token punctuation">,</span> <span class="token comment">-- 入职日期</span>
  salary <span class="token keyword">DECIMAL</span><span class="token punctuation">(</span><span class="token number">7</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token comment">-- 工资</span>
  bonus <span class="token keyword">DECIMAL</span><span class="token punctuation">(</span><span class="token number">7</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token comment">-- 奖金</span>
  dept_id <span class="token keyword">INT</span><span class="token punctuation">,</span> <span class="token comment">-- 所在部门编号</span>
  <span class="token keyword">CONSTRAINT</span> emp_jobid_ref_job_id_fk <span class="token keyword">FOREIGN</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span>job_id<span class="token punctuation">)</span> <span class="token keyword">REFERENCES</span> job <span class="token punctuation">(</span>id<span class="token punctuation">)</span><span class="token punctuation">,</span>
  <span class="token keyword">CONSTRAINT</span> emp_deptid_ref_dept_id_fk <span class="token keyword">FOREIGN</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span>dept_id<span class="token punctuation">)</span> <span class="token keyword">REFERENCES</span> dept <span class="token punctuation">(</span>id<span class="token punctuation">)</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment">-- 添加员工</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> emp<span class="token punctuation">(</span>id<span class="token punctuation">,</span>ename<span class="token punctuation">,</span>job_id<span class="token punctuation">,</span>mgr<span class="token punctuation">,</span>joindate<span class="token punctuation">,</span>salary<span class="token punctuation">,</span>bonus<span class="token punctuation">,</span>dept_id<span class="token punctuation">)</span> <span class="token keyword">VALUES</span> 
<span class="token punctuation">(</span><span class="token number">1001</span><span class="token punctuation">,</span><span class="token string">'孙悟空'</span><span class="token punctuation">,</span><span class="token number">4</span><span class="token punctuation">,</span><span class="token number">1004</span><span class="token punctuation">,</span><span class="token string">'2000-12-17'</span><span class="token punctuation">,</span><span class="token string">'8000.00'</span><span class="token punctuation">,</span><span class="token boolean">NULL</span><span class="token punctuation">,</span><span class="token number">20</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token punctuation">(</span><span class="token number">1002</span><span class="token punctuation">,</span><span class="token string">'卢俊义'</span><span class="token punctuation">,</span><span class="token number">3</span><span class="token punctuation">,</span><span class="token number">1006</span><span class="token punctuation">,</span><span class="token string">'2001-02-20'</span><span class="token punctuation">,</span><span class="token string">'16000.00'</span><span class="token punctuation">,</span><span class="token string">'3000.00'</span><span class="token punctuation">,</span><span class="token number">30</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token punctuation">(</span><span class="token number">1003</span><span class="token punctuation">,</span><span class="token string">'林冲'</span><span class="token punctuation">,</span><span class="token number">3</span><span class="token punctuation">,</span><span class="token number">1006</span><span class="token punctuation">,</span><span class="token string">'2001-02-22'</span><span class="token punctuation">,</span><span class="token string">'12500.00'</span><span class="token punctuation">,</span><span class="token string">'5000.00'</span><span class="token punctuation">,</span><span class="token number">30</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token punctuation">(</span><span class="token number">1004</span><span class="token punctuation">,</span><span class="token string">'唐僧'</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token number">1009</span><span class="token punctuation">,</span><span class="token string">'2001-04-02'</span><span class="token punctuation">,</span><span class="token string">'29750.00'</span><span class="token punctuation">,</span><span class="token boolean">NULL</span><span class="token punctuation">,</span><span class="token number">20</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token punctuation">(</span><span class="token number">1005</span><span class="token punctuation">,</span><span class="token string">'李逵'</span><span class="token punctuation">,</span><span class="token number">4</span><span class="token punctuation">,</span><span class="token number">1006</span><span class="token punctuation">,</span><span class="token string">'2001-09-28'</span><span class="token punctuation">,</span><span class="token string">'12500.00'</span><span class="token punctuation">,</span><span class="token string">'14000.00'</span><span class="token punctuation">,</span><span class="token number">30</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token punctuation">(</span><span class="token number">1006</span><span class="token punctuation">,</span><span class="token string">'宋江'</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token number">1009</span><span class="token punctuation">,</span><span class="token string">'2001-05-01'</span><span class="token punctuation">,</span><span class="token string">'28500.00'</span><span class="token punctuation">,</span><span class="token boolean">NULL</span><span class="token punctuation">,</span><span class="token number">30</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token punctuation">(</span><span class="token number">1007</span><span class="token punctuation">,</span><span class="token string">'刘备'</span><span class="token punctuation">,</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token number">1009</span><span class="token punctuation">,</span><span class="token string">'2001-09-01'</span><span class="token punctuation">,</span><span class="token string">'24500.00'</span><span class="token punctuation">,</span><span class="token boolean">NULL</span><span class="token punctuation">,</span><span class="token number">10</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token punctuation">(</span><span class="token number">1008</span><span class="token punctuation">,</span><span class="token string">'猪八戒'</span><span class="token punctuation">,</span><span class="token number">4</span><span class="token punctuation">,</span><span class="token number">1004</span><span class="token punctuation">,</span><span class="token string">'2007-04-19'</span><span class="token punctuation">,</span><span class="token string">'30000.00'</span><span class="token punctuation">,</span><span class="token boolean">NULL</span><span class="token punctuation">,</span><span class="token number">20</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token punctuation">(</span><span class="token number">1009</span><span class="token punctuation">,</span><span class="token string">'罗贯中'</span><span class="token punctuation">,</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token boolean">NULL</span><span class="token punctuation">,</span><span class="token string">'2001-11-17'</span><span class="token punctuation">,</span><span class="token string">'50000.00'</span><span class="token punctuation">,</span><span class="token boolean">NULL</span><span class="token punctuation">,</span><span class="token number">10</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token punctuation">(</span><span class="token number">1010</span><span class="token punctuation">,</span><span class="token string">'吴用'</span><span class="token punctuation">,</span><span class="token number">3</span><span class="token punctuation">,</span><span class="token number">1006</span><span class="token punctuation">,</span><span class="token string">'2001-09-08'</span><span class="token punctuation">,</span><span class="token string">'15000.00'</span><span class="token punctuation">,</span><span class="token string">'0.00'</span><span class="token punctuation">,</span><span class="token number">30</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token punctuation">(</span><span class="token number">1011</span><span class="token punctuation">,</span><span class="token string">'沙僧'</span><span class="token punctuation">,</span><span class="token number">4</span><span class="token punctuation">,</span><span class="token number">1004</span><span class="token punctuation">,</span><span class="token string">'2007-05-23'</span><span class="token punctuation">,</span><span class="token string">'11000.00'</span><span class="token punctuation">,</span><span class="token boolean">NULL</span><span class="token punctuation">,</span><span class="token number">20</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token punctuation">(</span><span class="token number">1012</span><span class="token punctuation">,</span><span class="token string">'李逵'</span><span class="token punctuation">,</span><span class="token number">4</span><span class="token punctuation">,</span><span class="token number">1006</span><span class="token punctuation">,</span><span class="token string">'2001-12-03'</span><span class="token punctuation">,</span><span class="token string">'9500.00'</span><span class="token punctuation">,</span><span class="token boolean">NULL</span><span class="token punctuation">,</span><span class="token number">30</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token punctuation">(</span><span class="token number">1013</span><span class="token punctuation">,</span><span class="token string">'小白龙'</span><span class="token punctuation">,</span><span class="token number">4</span><span class="token punctuation">,</span><span class="token number">1004</span><span class="token punctuation">,</span><span class="token string">'2001-12-03'</span><span class="token punctuation">,</span><span class="token string">'30000.00'</span><span class="token punctuation">,</span><span class="token boolean">NULL</span><span class="token punctuation">,</span><span class="token number">20</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token punctuation">(</span><span class="token number">1014</span><span class="token punctuation">,</span><span class="token string">'关羽'</span><span class="token punctuation">,</span><span class="token number">4</span><span class="token punctuation">,</span><span class="token number">1007</span><span class="token punctuation">,</span><span class="token string">'2002-01-23'</span><span class="token punctuation">,</span><span class="token string">'13000.00'</span><span class="token punctuation">,</span><span class="token boolean">NULL</span><span class="token punctuation">,</span><span class="token number">10</span><span class="token punctuation">)</span><span class="token punctuation">;</span>



<span class="token comment">-- 工资等级表</span>
<span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> salarygrade <span class="token punctuation">(</span>
  grade <span class="token keyword">INT</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">,</span>   <span class="token comment">-- 级别</span>
  losalary <span class="token keyword">INT</span><span class="token punctuation">,</span>  <span class="token comment">-- 最低工资</span>
  hisalary <span class="token keyword">INT</span> <span class="token comment">-- 最高工资</span>
<span class="token punctuation">)</span><span class="token punctuation">;</span>

<span class="token comment">-- 添加5个工资等级</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> salarygrade<span class="token punctuation">(</span>grade<span class="token punctuation">,</span>losalary<span class="token punctuation">,</span>hisalary<span class="token punctuation">)</span> <span class="token keyword">VALUES</span> 
<span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">,</span><span class="token number">7000</span><span class="token punctuation">,</span><span class="token number">12000</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">,</span><span class="token number">12010</span><span class="token punctuation">,</span><span class="token number">14000</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token punctuation">(</span><span class="token number">3</span><span class="token punctuation">,</span><span class="token number">14010</span><span class="token punctuation">,</span><span class="token number">20000</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token punctuation">(</span><span class="token number">4</span><span class="token punctuation">,</span><span class="token number">20010</span><span class="token punctuation">,</span><span class="token number">30000</span><span class="token punctuation">)</span><span class="token punctuation">,</span>
<span class="token punctuation">(</span><span class="token number">5</span><span class="token punctuation">,</span><span class="token number">30010</span><span class="token punctuation">,</span><span class="token number">99990</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br><span class="line-number">20</span><br><span class="line-number">21</span><br><span class="line-number">22</span><br><span class="line-number">23</span><br><span class="line-number">24</span><br><span class="line-number">25</span><br><span class="line-number">26</span><br><span class="line-number">27</span><br><span class="line-number">28</span><br><span class="line-number">29</span><br><span class="line-number">30</span><br><span class="line-number">31</span><br><span class="line-number">32</span><br><span class="line-number">33</span><br><span class="line-number">34</span><br><span class="line-number">35</span><br><span class="line-number">36</span><br><span class="line-number">37</span><br><span class="line-number">38</span><br><span class="line-number">39</span><br><span class="line-number">40</span><br><span class="line-number">41</span><br><span class="line-number">42</span><br><span class="line-number">43</span><br><span class="line-number">44</span><br><span class="line-number">45</span><br><span class="line-number">46</span><br><span class="line-number">47</span><br><span class="line-number">48</span><br><span class="line-number">49</span><br><span class="line-number">50</span><br><span class="line-number">51</span><br><span class="line-number">52</span><br><span class="line-number">53</span><br><span class="line-number">54</span><br><span class="line-number">55</span><br><span class="line-number">56</span><br><span class="line-number">57</span><br><span class="line-number">58</span><br><span class="line-number">59</span><br><span class="line-number">60</span><br><span class="line-number">61</span><br><span class="line-number">62</span><br><span class="line-number">63</span><br><span class="line-number">64</span><br><span class="line-number">65</span><br><span class="line-number">66</span><br><span class="line-number">67</span><br><span class="line-number">68</span><br><span class="line-number">69</span><br><span class="line-number">70</span><br><span class="line-number">71</span><br><span class="line-number">72</span><br><span class="line-number">73</span><br><span class="line-number">74</span><br><span class="line-number">75</span><br><span class="line-number">76</span><br><span class="line-number">77</span><br><span class="line-number">78</span><br><span class="line-number">79</span><br><span class="line-number">80</span><br><span class="line-number">81</span><br><span class="line-number">82</span><br></div></div><ul><li>需求</li></ul> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token comment">-- 需求：</span>

<span class="token comment">-- 1.查询所有员工信息。查询员工编号，员工姓名，工资，职务名称，职务描述</span>



<span class="token comment">-- 2.查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置</span>
   
<span class="token comment">-- 3.查询员工姓名，工资，工资等级</span>

<span class="token comment">-- 4.查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级</span>

<span class="token comment">-- 5.查询出部门编号、部门名称、部门位置、部门人数</span>
 
<span class="token comment">-- 6.查询所有员工的姓名及其直接上级的姓名,没有领导的员工也需要查询</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br></div></div><ul><li>答案</li></ul> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code>

<span class="token comment">-- 多表查询的练习</span>


<span class="token comment">-- 需求：</span>
			
<span class="token comment">-- 1.查询所有员工信息。查询员工编号，员工姓名，工资，职务名称，职务描述</span>
<span class="token comment">/*
	分析：
		1.员工编号，员工姓名，工资，需要查询emp表  职务名称，职务描述 需要查询job表
		2.查询条件 emp.job_id = job.id

*/</span>
<span class="token keyword">SELECT</span> 
	t1<span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>id<span class="token punctuation">`</span></span><span class="token punctuation">,</span> <span class="token comment">-- 员工编号</span>
	t1<span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>ename<span class="token punctuation">`</span></span><span class="token punctuation">,</span> <span class="token comment">-- 员工姓名</span>
	t1<span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>salary<span class="token punctuation">`</span></span><span class="token punctuation">,</span><span class="token comment">-- 工资</span>
	t2<span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>jname<span class="token punctuation">`</span></span><span class="token punctuation">,</span> <span class="token comment">-- 职务名称</span>
	t2<span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>description<span class="token punctuation">`</span></span> <span class="token comment">-- 职务描述</span>
<span class="token keyword">FROM</span> 
	emp t1<span class="token punctuation">,</span> job t2
<span class="token keyword">WHERE</span> 
	t1<span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>job_id<span class="token punctuation">`</span></span> <span class="token operator">=</span> t2<span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>id<span class="token punctuation">`</span></span><span class="token punctuation">;</span>
	
	
	
<span class="token comment">-- 2.查询员工编号，员工姓名，工资，职务名称，职务描述，部门名称，部门位置</span>
<span class="token comment">/*
	分析：
		1. 员工编号，员工姓名，工资 emp  职务名称，职务描述 job  部门名称，部门位置 dept
		2. 条件： emp.job_id = job.id and emp.dept_id = dept.id
*/</span>

<span class="token keyword">SELECT</span> 
	t1<span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>id<span class="token punctuation">`</span></span><span class="token punctuation">,</span> <span class="token comment">-- 员工编号</span>
	t1<span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>ename<span class="token punctuation">`</span></span><span class="token punctuation">,</span> <span class="token comment">-- 员工姓名</span>
	t1<span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>salary<span class="token punctuation">`</span></span><span class="token punctuation">,</span><span class="token comment">-- 工资</span>
	t2<span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>jname<span class="token punctuation">`</span></span><span class="token punctuation">,</span> <span class="token comment">-- 职务名称</span>
	t2<span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>description<span class="token punctuation">`</span></span><span class="token punctuation">,</span> <span class="token comment">-- 职务描述</span>
	t3<span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>dname<span class="token punctuation">`</span></span><span class="token punctuation">,</span> <span class="token comment">-- 部门名称</span>
	t3<span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>loc<span class="token punctuation">`</span></span> <span class="token comment">-- 部门位置</span>
<span class="token keyword">FROM</span> 
	emp t1<span class="token punctuation">,</span> job t2<span class="token punctuation">,</span>dept t3
<span class="token keyword">WHERE</span> 
	t1<span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>job_id<span class="token punctuation">`</span></span> <span class="token operator">=</span> t2<span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>id<span class="token punctuation">`</span></span> <span class="token operator">AND</span> t1<span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>dept_id<span class="token punctuation">`</span></span> <span class="token operator">=</span> t3<span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>id<span class="token punctuation">`</span></span><span class="token punctuation">;</span>
   
<span class="token comment">-- 3.查询员工姓名，工资，工资等级</span>
<span class="token comment">/*
	分析：
		1.员工姓名，工资 emp  工资等级 salarygrade
		2.条件 emp.salary &gt;= salarygrade.losalary and emp.salary &lt;= salarygrade.hisalary
			emp.salary BETWEEN salarygrade.losalary and salarygrade.hisalary
*/</span>
<span class="token keyword">SELECT</span> 
	t1<span class="token punctuation">.</span>ename <span class="token punctuation">,</span>
	t1<span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>salary<span class="token punctuation">`</span></span><span class="token punctuation">,</span>
	t2<span class="token punctuation">.</span><span class="token operator">*</span>
<span class="token keyword">FROM</span> emp t1<span class="token punctuation">,</span> salarygrade t2
<span class="token keyword">WHERE</span> t1<span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>salary<span class="token punctuation">`</span></span> <span class="token operator">BETWEEN</span> t2<span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>losalary<span class="token punctuation">`</span></span> <span class="token operator">AND</span> t2<span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>hisalary<span class="token punctuation">`</span></span><span class="token punctuation">;</span>



<span class="token comment">-- 4.查询员工姓名，工资，职务名称，职务描述，部门名称，部门位置，工资等级</span>
<span class="token comment">/*
	分析：
		1. 员工姓名，工资 emp ， 职务名称，职务描述 job 部门名称，部门位置，dept  工资等级 salarygrade
		2. 条件： emp.job_id = job.id and emp.dept_id = dept.id and emp.salary BETWEEN salarygrade.losalary and salarygrade.hisalary
			
*/</span>
<span class="token keyword">SELECT</span> 
	t1<span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>ename<span class="token punctuation">`</span></span><span class="token punctuation">,</span>
	t1<span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>salary<span class="token punctuation">`</span></span><span class="token punctuation">,</span>
	t2<span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>jname<span class="token punctuation">`</span></span><span class="token punctuation">,</span>
	t2<span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>description<span class="token punctuation">`</span></span><span class="token punctuation">,</span>
	t3<span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>dname<span class="token punctuation">`</span></span><span class="token punctuation">,</span>
	t3<span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>loc<span class="token punctuation">`</span></span><span class="token punctuation">,</span>
	t4<span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>grade<span class="token punctuation">`</span></span>
<span class="token keyword">FROM</span> 
	emp t1<span class="token punctuation">,</span>job t2<span class="token punctuation">,</span>dept t3<span class="token punctuation">,</span>salarygrade t4
<span class="token keyword">WHERE</span> 
	t1<span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>job_id<span class="token punctuation">`</span></span> <span class="token operator">=</span> t2<span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>id<span class="token punctuation">`</span></span> 
	<span class="token operator">AND</span> t1<span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>dept_id<span class="token punctuation">`</span></span> <span class="token operator">=</span> t3<span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>id<span class="token punctuation">`</span></span>
	<span class="token operator">AND</span> t1<span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>salary<span class="token punctuation">`</span></span> <span class="token operator">BETWEEN</span> t4<span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>losalary<span class="token punctuation">`</span></span> <span class="token operator">AND</span> t4<span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>hisalary<span class="token punctuation">`</span></span><span class="token punctuation">;</span>
	
	
	
<span class="token comment">-- 5.查询出部门编号、部门名称、部门位置、部门人数</span>

<span class="token comment">/*
	分析：
		1.部门编号、部门名称、部门位置 dept 表。 部门人数 emp表
		2.使用分组查询。按照emp.dept_id完成分组，查询count(id)
		3.使用子查询将第2步的查询结果和dept表进行关联查询
		
*/</span>
<span class="token keyword">SELECT</span> 
	t1<span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>id<span class="token punctuation">`</span></span><span class="token punctuation">,</span>t1<span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>dname<span class="token punctuation">`</span></span><span class="token punctuation">,</span>t1<span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>loc<span class="token punctuation">`</span></span> <span class="token punctuation">,</span> t2<span class="token punctuation">.</span>total
<span class="token keyword">FROM</span> 
	dept t1<span class="token punctuation">,</span>
	<span class="token punctuation">(</span><span class="token keyword">SELECT</span>
		dept_id<span class="token punctuation">,</span><span class="token function">COUNT</span><span class="token punctuation">(</span>id<span class="token punctuation">)</span> total
	<span class="token keyword">FROM</span> 
		emp
	<span class="token keyword">GROUP</span> <span class="token keyword">BY</span> dept_id<span class="token punctuation">)</span> t2
<span class="token keyword">WHERE</span> t1<span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>id<span class="token punctuation">`</span></span> <span class="token operator">=</span> t2<span class="token punctuation">.</span>dept_id<span class="token punctuation">;</span>



<span class="token comment">-- 6.查询所有员工的姓名及其直接上级的姓名,没有领导的员工也需要查询</span>

<span class="token comment">/*
	分析：
		1.姓名 emp， 直接上级的姓名 emp
			* emp表的id 和 mgr 是自关联
		2.条件 emp.id = emp.mgr
		3.查询左表的所有数据，和 交集数据
			* 使用左外连接查询
	
*/</span>
<span class="token comment">/*
select
	t1.ename,
	t1.mgr,
	t2.`id`,
	t2.ename
from emp t1, emp t2
where t1.mgr = t2.`id`;

*/</span>

<span class="token keyword">SELECT</span> 
	t1<span class="token punctuation">.</span>ename<span class="token punctuation">,</span>
	t1<span class="token punctuation">.</span>mgr<span class="token punctuation">,</span>
	t2<span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>id<span class="token punctuation">`</span></span><span class="token punctuation">,</span>
	t2<span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>ename<span class="token punctuation">`</span></span>
<span class="token keyword">FROM</span> emp t1
<span class="token keyword">LEFT</span> <span class="token keyword">JOIN</span> emp t2
<span class="token keyword">ON</span> t1<span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>mgr<span class="token punctuation">`</span></span> <span class="token operator">=</span> t2<span class="token punctuation">.</span><span class="token identifier"><span class="token punctuation">`</span>id<span class="token punctuation">`</span></span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br><span class="line-number">4</span><br><span class="line-number">5</span><br><span class="line-number">6</span><br><span class="line-number">7</span><br><span class="line-number">8</span><br><span class="line-number">9</span><br><span class="line-number">10</span><br><span class="line-number">11</span><br><span class="line-number">12</span><br><span class="line-number">13</span><br><span class="line-number">14</span><br><span class="line-number">15</span><br><span class="line-number">16</span><br><span class="line-number">17</span><br><span class="line-number">18</span><br><span class="line-number">19</span><br><span class="line-number">20</span><br><span class="line-number">21</span><br><span class="line-number">22</span><br><span class="line-number">23</span><br><span class="line-number">24</span><br><span class="line-number">25</span><br><span class="line-number">26</span><br><span class="line-number">27</span><br><span class="line-number">28</span><br><span class="line-number">29</span><br><span class="line-number">30</span><br><span class="line-number">31</span><br><span class="line-number">32</span><br><span class="line-number">33</span><br><span class="line-number">34</span><br><span class="line-number">35</span><br><span class="line-number">36</span><br><span class="line-number">37</span><br><span class="line-number">38</span><br><span class="line-number">39</span><br><span class="line-number">40</span><br><span class="line-number">41</span><br><span class="line-number">42</span><br><span class="line-number">43</span><br><span class="line-number">44</span><br><span class="line-number">45</span><br><span class="line-number">46</span><br><span class="line-number">47</span><br><span class="line-number">48</span><br><span class="line-number">49</span><br><span class="line-number">50</span><br><span class="line-number">51</span><br><span class="line-number">52</span><br><span class="line-number">53</span><br><span class="line-number">54</span><br><span class="line-number">55</span><br><span class="line-number">56</span><br><span class="line-number">57</span><br><span class="line-number">58</span><br><span class="line-number">59</span><br><span class="line-number">60</span><br><span class="line-number">61</span><br><span class="line-number">62</span><br><span class="line-number">63</span><br><span class="line-number">64</span><br><span class="line-number">65</span><br><span class="line-number">66</span><br><span class="line-number">67</span><br><span class="line-number">68</span><br><span class="line-number">69</span><br><span class="line-number">70</span><br><span class="line-number">71</span><br><span class="line-number">72</span><br><span class="line-number">73</span><br><span class="line-number">74</span><br><span class="line-number">75</span><br><span class="line-number">76</span><br><span class="line-number">77</span><br><span class="line-number">78</span><br><span class="line-number">79</span><br><span class="line-number">80</span><br><span class="line-number">81</span><br><span class="line-number">82</span><br><span class="line-number">83</span><br><span class="line-number">84</span><br><span class="line-number">85</span><br><span class="line-number">86</span><br><span class="line-number">87</span><br><span class="line-number">88</span><br><span class="line-number">89</span><br><span class="line-number">90</span><br><span class="line-number">91</span><br><span class="line-number">92</span><br><span class="line-number">93</span><br><span class="line-number">94</span><br><span class="line-number">95</span><br><span class="line-number">96</span><br><span class="line-number">97</span><br><span class="line-number">98</span><br><span class="line-number">99</span><br><span class="line-number">100</span><br><span class="line-number">101</span><br><span class="line-number">102</span><br><span class="line-number">103</span><br><span class="line-number">104</span><br><span class="line-number">105</span><br><span class="line-number">106</span><br><span class="line-number">107</span><br><span class="line-number">108</span><br><span class="line-number">109</span><br><span class="line-number">110</span><br><span class="line-number">111</span><br><span class="line-number">112</span><br><span class="line-number">113</span><br><span class="line-number">114</span><br><span class="line-number">115</span><br><span class="line-number">116</span><br><span class="line-number">117</span><br><span class="line-number">118</span><br><span class="line-number">119</span><br><span class="line-number">120</span><br><span class="line-number">121</span><br><span class="line-number">122</span><br><span class="line-number">123</span><br><span class="line-number">124</span><br><span class="line-number">125</span><br><span class="line-number">126</span><br><span class="line-number">127</span><br><span class="line-number">128</span><br><span class="line-number">129</span><br><span class="line-number">130</span><br><span class="line-number">131</span><br><span class="line-number">132</span><br><span class="line-number">133</span><br><span class="line-number">134</span><br><span class="line-number">135</span><br><span class="line-number">136</span><br><span class="line-number">137</span><br><span class="line-number">138</span><br><span class="line-number">139</span><br></div></div><h2 id="_6-事务"><a href="#_6-事务" class="header-anchor">#</a> 6.事务</h2> <h3 id="_6-1-事务的介绍"><a href="#_6-1-事务的介绍" class="header-anchor">#</a> 6.1 事务的介绍</h3> <ul><li><strong>==概念：如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败==</strong></li> <li>操作
<ol><li><strong>开启事务：start transaction</strong></li> <li><strong>回滚：rollback</strong></li> <li><strong>提交：commit</strong></li></ol></li> <li>图示</li></ul> <p><img src="https://img-blog.csdnimg.cn/a97f4cc650fe4039831b69f265c77513.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5YeM5q2G,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center" alt="在这里插入图片描述"></p> <h3 id="_6-2-事务提交的两种方式"><a href="#_6-2-事务提交的两种方式" class="header-anchor">#</a> 6.2 事务提交的两种方式</h3> <ul><li><p>自动提交</p> <ul><li><strong>Mysql默认是自动提交的</strong></li> <li><strong>一条DML(增删改)语句会自动提交一次事务</strong></li></ul></li> <li><p>手动提交</p> <ul><li><strong>Oracle默认是手动提交的，要想DML对数据的操作永久生效，需要commit</strong></li></ul></li> <li><p>修改提交方式</p> <ul><li><strong>查看默认提交方式</strong></li></ul> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SELECT</span> @<span class="token variable">@autocommit</span><span class="token punctuation">;</span>  <span class="token comment">-- 自动提交的值为1，手动提交的值未0</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p>-<img src="https://img-blog.csdnimg.cn/e5ada142725346c7803a0258efccec37.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5YeM5q2G,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center" alt="在这里插入图片描述"></p> <ul><li><strong>修改默认提交方式</strong></li></ul> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">SET</span> @<span class="token variable">@autocommit</span><span class="token operator">=</span><span class="token number">0</span><span class="token punctuation">;</span>

<span class="token keyword">SET</span> @<span class="token variable">@autocommit</span><span class="token operator">=</span><span class="token number">1</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div></li></ul> <h3 id="_6-3-事务的四大特征"><a href="#_6-3-事务的四大特征" class="header-anchor">#</a> 6.3 事务的四大特征</h3> <ul><li>==<strong>原子性：不可分隔的最小操作单位，要么同时成功，要么同时失败</strong>==</li> <li>==<strong>持久性：当事务提交或者回滚后，数据库会持久化的保存数据</strong>==</li> <li>==<strong>隔离性：多个事务之间，相互独立</strong>==</li> <li>==<strong>一致性：事务操作前后，数据的总量不变</strong>==</li></ul> <h3 id="_6-4-事务的不同隔离级别"><a href="#_6-4-事务的不同隔离级别" class="header-anchor">#</a> 6.4 事务的不同隔离级别</h3> <ul><li><p><strong>多个事务之间是隔离的，相互独立的，但是如果多个事务操作同一批数据，则会引发一系列问题</strong></p></li> <li><p>存在问题</p> <ul><li><strong>脏读：一个事务，读取到另一个事务中未提交的数据</strong></li> <li><strong>不可重复读：同一个事务中，两次读取到的数据不相同</strong></li> <li><strong>幻读，一个事务给所有数据进行了DML操作，而另一个事务添加一条记录，则第一个事务查询不到自己所做的修改</strong></li></ul></li> <li><p><strong>隔离级别</strong></p> <ul><li><strong>read uncommited:读未提交</strong> <ul><li><strong>问题：脏读，不可重复读，幻读</strong></li></ul></li> <li><strong>read commited:读已提交(oracle)</strong> <ul><li><strong>问题:不可重复读，幻读</strong></li></ul></li> <li><strong>repeatable-read:可重复读(mysql)</strong> <ul><li><strong>问题：幻读</strong></li></ul></li> <li><strong>serializable:串行执行</strong> <ul><li><strong>所有问题都解决</strong></li></ul></li></ul></li> <li><p><strong>随着隔离级别的升高，安全性变高，效率变低</strong></p></li> <li><p><strong>数据库查询隔离级别</strong></p> <ul><li><strong>==select @@tx_isolation==</strong></li> <li><img src="https://img-blog.csdnimg.cn/463c6ff6da8546ca8b3fa05d17dbc58b.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5YeM5q2G,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center" alt="在这里插入图片描述"></li></ul></li> <li><p>数据库修改隔离级别</p> <ul><li><strong>==set global transaction isolation level 级别字符串==</strong></li></ul></li></ul> <h2 id="_7-dcl-管理用户-授权"><a href="#_7-dcl-管理用户-授权" class="header-anchor">#</a> 7.DCL(管理用户，授权)</h2> <h3 id="_7-1管理用户"><a href="#_7-1管理用户" class="header-anchor">#</a> 7.1管理用户</h3> <ol><li><p>添加用户:</p> <ol><li><p><strong>==创建用户==</strong></p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">create</span> <span class="token keyword">user</span> <span class="token string">'用户名'</span><span class="token variable">@'主机名'</span> identified <span class="token keyword">by</span> <span class="token string">'密码'</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div></li></ol> <p><img src="https://img-blog.csdnimg.cn/4996d506165c4ff4bf114fa621d4694d.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5YeM5q2G,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center" alt="在这里插入图片描述"></p></li> <li><p>删除用户:</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">drop</span> <span class="token keyword">user</span> <span class="token string">'用户名'</span><span class="token variable">@'主机名'</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p><img src="https://img-blog.csdnimg.cn/d21deea0354e40d2a4edae5e0cf67fe1.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5YeM5q2G,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center" alt="在这里插入图片描述"></p></li> <li><p>修改用户密码:</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">update</span> <span class="token keyword">user</span> <span class="token keyword">set</span> password<span class="token operator">=</span>password<span class="token punctuation">(</span><span class="token string">'新密码'</span><span class="token punctuation">)</span> <span class="token keyword">where</span> <span class="token keyword">user</span><span class="token operator">=</span><span class="token string">'用户名'</span><span class="token punctuation">;</span>

<span class="token keyword">set</span> password <span class="token keyword">for</span> <span class="token string">'用户名'</span><span class="token variable">@'主机名'</span><span class="token operator">=</span>password<span class="token punctuation">(</span><span class="token string">'新密码'</span><span class="token punctuation">)</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div></li></ol> <p><img src="https://img-blog.csdnimg.cn/070460cc29ff43ea9d6055c1ec8d4fb3.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5YeM5q2G,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center" alt="在这里插入图片描述"></p> <p><img src="https://img-blog.csdnimg.cn/75f1375a33e347d4800bc2eca9071e33.gif#pic_center" alt="在这里插入图片描述"></p> <ol start="4"><li><p><strong>查询用户:</strong></p> <ol><li><strong>切换到mysql数据库</strong></li> <li><strong>查询user表</strong></li></ol> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">USE</span>  mysql<span class="token punctuation">;</span>

<span class="token keyword">SELECT</span> <span class="token operator">*</span><span class="token keyword">FROM</span> <span class="token keyword">USER</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br><span class="line-number">2</span><br><span class="line-number">3</span><br></div></div><ul><li><strong>通配符：%表示可以在任意主机使用用户登录</strong></li></ul></li></ol> <h3 id="_7-2-mysql忘记了root用户的密码"><a href="#_7-2-mysql忘记了root用户的密码" class="header-anchor">#</a> 7.2 mysql忘记了root用户的密码</h3> <ol><li><strong>cmd -- &gt; net stop mysql 停止mysql服务</strong></li></ol> <ul><li><strong>需要管理员运行该cmd</strong></li></ul> <ol start="2"><li><strong>使用无验证方式启动mysql服务： mysqld --skip-grant-tables</strong></li> <li><strong>打开新的cmd窗口,直接输入mysql命令，敲回车。就可以登录成功</strong></li> <li><strong>use mysql;</strong></li> <li><strong>update user set password = password('你的新密码') where user = 'root';</strong></li> <li><strong>关闭两个窗口</strong></li> <li><strong>打开任务管理器，手动结束mysqld.exe 的进程</strong></li> <li><strong>启动mysql服务</strong></li> <li><strong>使用新密码登录。</strong></li></ol> <h3 id="_7-3-管理权限"><a href="#_7-3-管理权限" class="header-anchor">#</a> 7.3 管理权限</h3> <ul><li><p>查询权限</p> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">show</span> grants <span class="token keyword">for</span> <span class="token string">'用户名'</span><span class="token variable">@'主机名'</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div></li> <li><p>授予权限</p></li></ul> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">grant</span> 权限列表 <span class="token keyword">on</span> 数据库名<span class="token punctuation">.</span>表名 <span class="token keyword">to</span> <span class="token string">'用户名'</span><span class="token variable">@'主机名'</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><p><img src="https://img-blog.csdnimg.cn/d4f7c98677654273bea5184fb2ffc645.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5YeM5q2G,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center" alt="在这里插入图片描述"></p> <ul><li>授予用户所有权限</li></ul> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">grant</span> <span class="token keyword">all</span> <span class="token keyword">on</span> <span class="token operator">*</span><span class="token punctuation">.</span><span class="token operator">*</span> <span class="token keyword">to</span> <span class="token string">'zhans'</span><span class="token variable">@'localhost'</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><ul><li>撤销权限</li></ul> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">revoke</span> 权限列表 <span class="token keyword">on</span> 数据库名<span class="token punctuation">.</span>表名 <span class="token keyword">from</span> <span class="token string">'用户名'</span><span class="token variable">@'主机名'</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div><ul><li>撤销所有权限</li></ul> <div class="language-sql line-numbers-mode"><pre class="language-sql"><code><span class="token keyword">revoke</span> <span class="token keyword">all</span> <span class="token keyword">on</span> <span class="token operator">*</span><span class="token punctuation">.</span><span class="token operator">*</span> <span class="token keyword">from</span> <span class="token string">'用户名'</span><span class="token variable">@'主机名'</span><span class="token punctuation">;</span>
</code></pre> <div class="line-numbers-wrapper"><span class="line-number">1</span><br></div></div></div></div>  <div class="page-edit"><div class="edit-link"><a href="https://github.com/linxin1123/vuepress-blog/edit/master/docs/02.后端/43.MySql笔记/03.MySql学习第三天.md" target="_blank" rel="noopener noreferrer">编辑</a> <span><svg xmlns="http://www.w3.org/2000/svg" aria-hidden="true" focusable="false" x="0px" y="0px" viewBox="0 0 100 100" width="15" height="15" class="icon outbound"><path fill="currentColor" d="M18.8,85.1h56l0,0c2.2,0,4-1.8,4-4v-32h-8v28h-48v-48h28v-8h-32l0,0c-2.2,0-4,1.8-4,4v56C14.8,83.3,16.6,85.1,18.8,85.1z"></path> <polygon fill="currentColor" points="45.7,48.7 51.3,54.3 77.2,28.5 77.2,37.2 85.2,37.2 85.2,14.9 62.8,14.9 62.8,22.9 71.5,22.9"></polygon></svg> <span class="sr-only">(opens new window)</span></span></div> <!----> <div class="last-updated"><span class="prefix">上次更新:</span> <span class="time">2023/02/17, 11:29:32</span></div></div> <div class="page-nav-wapper"><div class="page-nav-centre-wrap"><a href="/pages/e13f8c/" class="page-nav-centre page-nav-centre-prev"><div class="tooltip">MySQL学习第二天</div></a> <a href="/pages/d90431/" class="page-nav-centre page-nav-centre-next"><div class="tooltip">SSM复习-cnblog</div></a></div> <div class="page-nav"><p class="inner"><span class="prev">
        ←
        <a href="/pages/e13f8c/" class="prev">MySQL学习第二天</a></span> <span class="next"><a href="/pages/d90431/">SSM复习-cnblog</a>→
      </span></p></div></div></div> <div class="article-list"><div class="article-title"><a href="/archives/" class="iconfont icon-bi">最近更新</a></div> <div class="article-wrapper"><dl><dd>01</dd> <dt><a href="/pages/0b785d/"><div>
            Vuex
            <!----></div></a> <span class="date">03-14</span></dt></dl><dl><dd>02</dd> <dt><a href="/pages/c59cc0/"><div>
            Vue响应式原理
            <!----></div></a> <span class="date">03-14</span></dt></dl><dl><dd>03</dd> <dt><a href="/pages/83cc7a/"><div>
            Vue虚拟DOM-cnblog
            <!----></div></a> <span class="date">03-14</span></dt></dl> <dl><dd></dd> <dt><a href="/archives/" class="more">更多文章&gt;</a></dt></dl></div></div></main></div> <div class="footer"><div class="icons"><a href="3010733382@qq.com" title="发邮件" target="_blank" class="iconfont icon-youjian"></a><a href="https://github.com/linxin1123" title="GitHub" target="_blank" class="iconfont icon-github"></a><a href="https://music.163.com/#/playlist?id=755597173" title="听音乐" target="_blank" class="iconfont icon-erji"></a></div> 
  Theme by
  <a href="https://github.com/xugaoyi/vuepress-theme-vdoing" target="_blank" title="本站主题">Vdoing</a> 
    | Copyright © 2022-2023
    <span>lingxin | <a href="https://github.com/linxin1123/vuepress-blog/blob/master/LICENSE" target="_blank">MIT License</a></span></div> <div class="buttons"><div title="返回顶部" class="button blur go-to-top iconfont icon-fanhuidingbu" style="display:none;"></div> <div title="去评论" class="button blur go-to-comment iconfont icon-pinglun" style="display:none;"></div> <div title="主题模式" class="button blur theme-mode-but iconfont icon-zhuti"><ul class="select-box" style="display:none;"><li class="iconfont icon-zidong">
          跟随系统
        </li><li class="iconfont icon-rijianmoshi">
          浅色模式
        </li><li class="iconfont icon-yejianmoshi">
          深色模式
        </li><li class="iconfont icon-yuedu">
          阅读模式
        </li></ul></div></div> <!----> <!----> <!----></div><div class="global-ui"><div></div></div></div>
    <script src="/assets/js/app.57550e13.js" defer></script><script src="/assets/js/2.f014c35f.js" defer></script><script src="/assets/js/93.c8b8e3d6.js" defer></script>
  </body>
</html>
